﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace GG.Data
{
    public class MyData
    {

        private string _conString = ConfigurationManager.ConnectionStrings["GG_DBConnectionString"].ToString();

        public MyData()
        {
        }

        public DataSet GetNewLeads()
        {
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();

            DataSet _dsAgentLeads = new DataSet();

            try
            {
                _sqlConn.ConnectionString = _conString;

                //DateTime d = GG.Global.General.GetUTCDateTime();
                //DateTime _dtDate = GG.Global.General.GetUTCDateTime();
                //string _strFromDate = _dtFromDate.Year.ToString("0000") + _dtFromDate.Month.ToString("00") + _dtFromDate.Day.ToString("00");
                //string _strToDate = _dtToDate.Year.ToString("0000") + _dtToDate.Month.ToString("00") + _dtToDate.Day.ToString("00");

                _sqlConn.Open();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocCXTHGetNewLeadList";
                //SqlParameter _sqlPara1 = new SqlParameter();
                //_sqlPara1.ParameterName = "@FromDate";
                //_sqlCmd.Parameters.Add(_sqlPara1);
                //_sqlCmd.Parameters[0].Value = _strFromDate;

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
                _sqlDa.Fill(_dsAgentLeads);

            }
            catch (Exception Ex)
            {
                Ex.ToString();

            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

            }


            return _dsAgentLeads;
        }

        public Int32 GetEventCount()
        {
            Int32 RecordCount = 0;
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();
            DataSet _dsEventCount = new DataSet();
            try
            {
                string strQuery = @"SELECT COUNT(*) AS RecordCount FROM tblCXCRMEvents ";
                strQuery += " WHERE CONVERT(VARCHAR(8), EventDate, 112) = CONVERT(VARCHAR(8),dbo.fn_getUTCDateTime(), 112)";
                _sqlConn.ConnectionString = _conString;
                _sqlConn.Open();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.Text;
                _sqlCmd.CommandText = strQuery;

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
                _sqlDa.Fill(_dsEventCount);
                if (_dsEventCount != null && _dsEventCount.Tables.Count > 0 && _dsEventCount.Tables[0].Rows.Count > 0)
                {
                    if (!Int32.TryParse(_dsEventCount.Tables[0].Rows[0]["RecordCount"].ToString(), out RecordCount))
                    {
                        RecordCount = 0;
                    }
                }
            }
            catch (Exception Ex)
            {
                Ex.ToString();

            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

            }

            return RecordCount;
        }

    }
}
